package com.micro.ai.auth.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.micro.ai.auth.entity.Menu;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * 菜单Mapper接口
 * 
 * @author micro-ai
 * @since 0.0.1
 */
@Mapper
public interface MenuMapper extends BaseMapper<Menu> {

    /**
     * 根据租户ID查询菜单列表
     * 
     * @param tenantId 租户ID
     * @return 菜单列表
     */
    @Select("SELECT * FROM menus WHERE tenant_id = #{tenantId} AND deleted_at IS NULL ORDER BY level, sort_order")
    List<Menu> selectByTenantId(@Param("tenantId") String tenantId);

    /**
     * 根据父菜单ID查询子菜单
     * 
     * @param parentId 父菜单ID
     * @return 子菜单列表
     */
    @Select("SELECT * FROM menus WHERE parent_id = #{parentId} AND deleted_at IS NULL ORDER BY sort_order")
    List<Menu> selectByParentId(@Param("parentId") String parentId);

    /**
     * 根据租户ID和父菜单ID查询子菜单
     * 
     * @param tenantId 租户ID
     * @param parentId 父菜单ID
     * @return 子菜单列表
     */
    @Select("SELECT * FROM menus WHERE tenant_id = #{tenantId} AND parent_id = #{parentId} AND deleted_at IS NULL ORDER BY sort_order")
    List<Menu> selectByTenantAndParentId(@Param("tenantId") String tenantId, @Param("parentId") String parentId);

    /**
     * 根据租户ID和菜单编码查询菜单
     * 
     * @param tenantId 租户ID
     * @param code 菜单编码
     * @return 菜单信息
     */
    @Select("SELECT * FROM menus WHERE tenant_id = #{tenantId} AND code = #{code} AND deleted_at IS NULL LIMIT 1")
    Menu selectByTenantAndCode(@Param("tenantId") String tenantId, @Param("code") String code);

    /**
     * 根据用户ID查询用户可访问的菜单
     * 
     * @param userId 用户ID
     * @param tenantId 租户ID
     * @return 菜单列表
     */
    @Select("""
        SELECT DISTINCT m.* FROM menus m
        LEFT JOIN role_menus rm ON m.id = rm.menu_id
        LEFT JOIN user_roles ur ON rm.role_id = ur.role_id
        WHERE ur.user_id = #{userId}
        AND m.tenant_id = #{tenantId}
        AND m.deleted_at IS NULL
        AND m.status = 'active'
        AND m.visible = 1
        ORDER BY m.level, m.sort_order
        """)
    List<Menu> selectByUserId(@Param("userId") String userId, @Param("tenantId") String tenantId);

    /**
     * 根据角色ID查询角色可访问的菜单
     * 
     * @param roleId 角色ID
     * @param tenantId 租户ID
     * @return 菜单列表
     */
    @Select("""
        SELECT DISTINCT m.* FROM menus m
        LEFT JOIN role_menus rm ON m.id = rm.menu_id
        WHERE rm.role_id = #{roleId}
        AND m.tenant_id = #{tenantId}
        AND m.deleted_at IS NULL
        AND m.status = 'active'
        AND m.visible = 1
        ORDER BY m.level, m.sort_order
        """)
    List<Menu> selectByRoleId(@Param("roleId") String roleId, @Param("tenantId") String tenantId);

    /**
     * 查询菜单树（递归查询所有子菜单）
     * 
     * @param tenantId 租户ID
     * @param parentId 父菜单ID（NULL表示查询根菜单）
     * @return 菜单树
     */
    @Select("""
        WITH RECURSIVE menu_tree AS (
            SELECT * FROM menus 
            WHERE tenant_id = #{tenantId} 
            AND (
                (#{parentId} IS NULL AND parent_id IS NULL) 
                OR parent_id = #{parentId}
            )
            AND deleted_at IS NULL
            UNION ALL
            SELECT m.* FROM menus m
            INNER JOIN menu_tree mt ON m.parent_id = mt.id
            WHERE m.deleted_at IS NULL
        )
        SELECT * FROM menu_tree ORDER BY level, sort_order
        """)
    List<Menu> selectMenuTree(@Param("tenantId") String tenantId, @Param("parentId") String parentId);

    /**
     * 检查菜单编码是否存在
     * 
     * @param tenantId 租户ID
     * @param code 菜单编码
     * @param excludeId 排除的菜单ID（用于更新时检查）
     * @return 存在数量
     */
    @Select("""
        SELECT COUNT(*) FROM menus 
        WHERE tenant_id = #{tenantId} 
        AND code = #{code} 
        AND deleted_at IS NULL
        AND (#{excludeId} IS NULL OR id != #{excludeId})
        """)
    int checkCodeExists(@Param("tenantId") String tenantId, @Param("code") String code, @Param("excludeId") String excludeId);

    /**
     * 检查菜单是否有子菜单
     * 
     * @param menuId 菜单ID
     * @return 子菜单数量
     */
    @Select("SELECT COUNT(*) FROM menus WHERE parent_id = #{menuId} AND deleted_at IS NULL")
    int countChildren(@Param("menuId") String menuId);

    /**
     * 更新菜单排序
     * 
     * @param menuId 菜单ID
     * @param sortOrder 排序
     */
    @Update("UPDATE menus SET sort_order = #{sortOrder}, updated_at = NOW() WHERE id = #{menuId}")
    void updateSortOrder(@Param("menuId") String menuId, @Param("sortOrder") Integer sortOrder);

    /**
     * 批量更新菜单排序
     * 
     * @param menuIds 菜单ID列表
     * @param sortOrders 排序列表
     */
    @Update("<script>" +
            "UPDATE menus SET sort_order = CASE id " +
            "<foreach collection='menuIds' index='index' item='menuId'>" +
            "WHEN #{menuId} THEN #{sortOrders[${index}]} " +
            "</foreach>" +
            "END, updated_at = NOW() " +
            "WHERE id IN " +
            "<foreach collection='menuIds' item='menuId' open='(' separator=',' close=')'>" +
            "#{menuId}" +
            "</foreach>" +
            "</script>")
    void batchUpdateSortOrder(@Param("menuIds") List<String> menuIds, @Param("sortOrders") List<Integer> sortOrders);

    /**
     * 统计角色菜单关联数量
     * 
     * @param menuId 菜单ID
     * @return 关联数量
     */
    @Select("SELECT COUNT(*) FROM role_menus WHERE menu_id = #{menuId}")
    int countRoleMenuPermissions(@Param("menuId") String menuId);

    /**
     * 统计用户菜单权限关联数量
     * 注意：新权限系统中没有user_menu_permissions表，用户通过角色获取菜单权限
     * 
     * @param menuId 菜单ID
     * @return 关联数量（始终返回0）
     */
    default int countUserMenuPermissions(@Param("menuId") String menuId) {
        // 新权限系统中用户通过角色获取菜单权限，没有直接的用户-菜单权限关联
        return 0;
    }
}
